using System;
using System.Collections.Generic;
using System.Text;
using Pg.BioMedics.RatObserver.Database.Model;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using FirebirdSql.Data.Isql;
using FirebirdSql.Data;
using FirebirdSql.Data.FirebirdClient;

namespace Pg.BioMedics.RatObserver.Database.DAO.Impl
{
    class VideoDAO : BaseDAO, IVideoDAO
    {
        public void saveVideo(VideoInfo vi)
        {
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "INSERT INTO VIDEO_INFO (FILE_NAME, ORIGINAL_FILE_NAME, NAME, FRAMES_COUNT, LENGHT, USER_DESCRIPTION, REMOVE_TIME) VALUES (@fileName, @originalFileName, @name, @framesCount, @lenght, @userDescription, @removeTime)";
            cmd.Parameters.Add("@fileName", FbDbType.VarChar).Value = vi.fileName;
            cmd.Parameters.Add("@originalFileName", FbDbType.VarChar).Value = vi.originalFileName;
            cmd.Parameters.Add("@name", FbDbType.VarChar).Value = vi.name;
            cmd.Parameters.Add("@framesCount", FbDbType.Integer).Value = vi.framesCount;
            cmd.Parameters.Add("@lenght", FbDbType.Integer).Value = vi.lenght;
            cmd.Parameters.Add("@userDescription", FbDbType.VarChar).Value = vi.userDescription;
            cmd.Parameters.Add("@removeTime", FbDbType.TimeStamp).Value = new DateTime(0);
            cmd.ExecuteNonQuery();
        }

        public void updateVideo(VideoInfo vi)
        {
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "UPDATE VIDEO_INFO SET NAME = @name, USER_DESCRIPTION = @userDescription WHERE ID = @id";
            cmd.Parameters.Add("@name", FbDbType.VarChar).Value = vi.name;
            cmd.Parameters.Add("@userDescription", FbDbType.VarChar).Value = vi.userDescription;
            cmd.Parameters.Add("@id", FbDbType.BigInt).Value = vi.id;
            cmd.ExecuteNonQuery();
        }

        public void removeVideo(VideoInfo vi)
        {
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "UPDATE VIDEO_INFO SET REMOVE_TIME = @removeTime WHERE ID = @id";
            cmd.Parameters.Add("@removeTime", FbDbType.TimeStamp).Value = DateTime.Now;
            cmd.Parameters.Add("@id", FbDbType.BigInt).Value = vi.id;
            cmd.ExecuteNonQuery();
        }

        public bool extistsVideo(VideoInfo vi)
        {
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "select ID FROM VIDEO_INFO WHERE NAME = @name AND REMOVE_TIME = @removeTime";
            cmd.Parameters.Add("@name", FbDbType.VarChar).Value = vi.name;
            cmd.Parameters.Add("@removeTime", FbDbType.TimeStamp).Value = new DateTime(0);
            IDataReader reader = cmd.ExecuteReader();
            bool res = false;
            while (reader.Read())
            {
                Int64 id = reader.GetInt64(0);
                res = !id.Equals(vi.id);
                if (!res)
                    break;
            }

            return res;
        }

        

        public IList<VideoInfo> findAll()
        {
            IList<VideoInfo> res = new List<VideoInfo>();
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "select ID, FILE_NAME, ORIGINAL_FILE_NAME, NAME, FRAMES_COUNT, LENGHT, USER_DESCRIPTION FROM VIDEO_INFO WHERE REMOVE_TIME = @removeTime";
            cmd.Parameters.Add("@removeTime", FbDbType.TimeStamp).Value = new DateTime(0);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                VideoInfo vi = new VideoInfo();
                vi.id = reader.GetInt64(0);
                vi.fileName = reader.GetString(1);
                vi.originalFileName = reader.GetString(2);
                vi.name = reader.GetString(3);
                vi.framesCount = reader.GetInt32(4);
               vi.lenght = reader.GetInt32(5);
               vi.userDescription = reader.GetString(6);
                res.Add(vi);
            }
            return res;
        }

        public IList<VideoInfo> getById()
        {
            IList<VideoInfo> res = new List<VideoInfo>();
            FbCommand cmd = getConnection().CreateCommand();
            cmd.CommandText = "select ID, FILE_NAME, ORIGINAL_FILE_NAME, NAME, FRAMES_COUNT, LENGHT, USER_DESCRIPTION FROM VIDEO_INFO WHERE ID = @id";
            cmd.Parameters.Add("@id", FbDbType.BigInt);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                VideoInfo vi = new VideoInfo();
                vi.id = reader.GetInt64(0);
                vi.fileName = reader.GetString(1);
                vi.originalFileName = reader.GetString(2);
                vi.name = reader.GetString(3);
                vi.framesCount = reader.GetInt32(4);
                vi.lenght = reader.GetInt32(5);
                vi.userDescription = reader.GetString(6);
                res.Add(vi);
            }
            return res;
        }
    }
}
